using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using AIM.Model;
using BrightFrame;

namespace AIM.DAL
{

    public class ArchiveTypeDAL
    {
        private DbProvider dbProvider = new DbProvider();

        public List<ArchiveType> QueryByPur(int fondsID, string archiveTypeIDs)
        {
            List<ArchiveType> archiveTypeList = new List<ArchiveType>();
            using (IDataReader dr = dbProvider.ExecuteDataReader(String.Format(@"
SELECT 
    ArchiveTypeID,
    ParentID,
    TypeName,
    Sort,
    FondsID,
    ArchiveKindID 
FROM archive_type
WHERE FondsID=?FondsID
AND ArchiveTypeID in ({0})", archiveTypeIDs),
                dbProvider.CreateParameter("?FondsID", DbType.Int32, fondsID)))
            {
                while (dr.Read())
                {
                    archiveTypeList.Add(new ArchiveType
                    {
                        ArchiveTypeID = Convert.ToInt32(dr["ArchiveTypeID"]),
                        ParentID = Convert.ToInt32(dr["ParentID"]),
                        TypeName = dr["TypeName"].ToString(),
                        Sort = Convert.ToInt32(dr["Sort"]),
                        Fonds = new Fonds
                        {
                            FondsID = Convert.ToInt32(dr["FondsID"])
                        },
                        ArchiveKind = new ArchiveKind
                        {
                            ArchiveKindID = dr["ArchiveKindID"].ToString()
                        }
                    });
                }
            }
            return archiveTypeList;
        }

        public List<ArchiveType> QueryByTree(int fondsID, int parentID)
        {
            List<ArchiveType> archiveTypeList = new List<ArchiveType>();
            using (IDataReader dr = dbProvider.ExecuteDataReader(@"
SELECT 
    ArchiveTypeID,
    ParentID,
    TypeName,
    Sort,
    FondsID,
    ArchiveKindID 
FROM archive_type
WHERE FondsID=?FondsID
AND ParentID=?ParentID
",
                dbProvider.CreateParameter("?FondsID", DbType.Int32, fondsID),
                dbProvider.CreateParameter("?ParentID", DbType.Int32, parentID)))
            {
                while (dr.Read())
                {
                    archiveTypeList.Add(new ArchiveType
                    {
                        ArchiveTypeID = Convert.ToInt32(dr["ArchiveTypeID"]),
                        ParentID = Convert.ToInt32(dr["ParentID"]),
                        TypeName = dr["TypeName"].ToString(),
                        Sort = Convert.ToInt32(dr["Sort"]),
                        Fonds = new Fonds
                        {
                            FondsID = Convert.ToInt32(dr["FondsID"])
                        },
                        ArchiveKind = new ArchiveKind
                        {
                            ArchiveKindID = dr["ArchiveKindID"].ToString()
                        }
                    });
                }
            }
            return archiveTypeList;
        }

        public List<ArchiveType> QueryByTreeAsPur(int fondsID, int parentID, string archiveTypeIDs)
        {
            List<ArchiveType> archiveTypeList = new List<ArchiveType>();
            using(IDataReader dr = dbProvider.ExecuteDataReader(String.Format(@"
SELECT 
    ArchiveTypeID,
    ParentID,
    TypeName,
    Sort,
    FondsID,
    ArchiveKindID 
FROM archive_type
WHERE FondsID=?FondsID
AND ParentID=?ParentID
AND ArchiveTypeID in ({0})", archiveTypeIDs),
                dbProvider.CreateParameter("?FondsID", DbType.Int32, fondsID),
                dbProvider.CreateParameter("?ParentID", DbType.Int32, parentID)))
            {
                while (dr.Read())
                {
                    archiveTypeList.Add(new ArchiveType
                    {
                        ArchiveTypeID = Convert.ToInt32(dr["ArchiveTypeID"]),
                        ParentID = Convert.ToInt32(dr["ParentID"]),
                        TypeName = dr["TypeName"].ToString(),
                        Sort = Convert.ToInt32(dr["Sort"]),
                        Fonds = new Fonds
                        {
                            FondsID = Convert.ToInt32(dr["FondsID"])
                        },
                        ArchiveKind = new ArchiveKind
                        {
                            ArchiveKindID = dr["ArchiveKindID"].ToString()
                        }
                    });
                }
            }
            return archiveTypeList;
        }

        public List<ArchiveType> QueryByFondID(int fondsID)
        {
            List<ArchiveType> archiveTypeList = new List<ArchiveType>();
            using (IDataReader dr = dbProvider.ExecuteDataReader(@"
SELECT 
    ArchiveTypeID,
    ParentID,
    TypeName,
    Sort,
    FondsID,
    ArchiveKindID 
FROM archive_type
WHERE FondsID=?FondsID
",
                dbProvider.CreateParameter("?FondsID", DbType.Int32, fondsID)))
            {
                while (dr.Read())
                {
                    archiveTypeList.Add(new ArchiveType
                    {
                        ArchiveTypeID = Convert.ToInt32(dr["ArchiveTypeID"]),
                        ParentID = Convert.ToInt32(dr["ParentID"]),
                        TypeName = dr["TypeName"].ToString(),
                        Sort = Convert.ToInt32(dr["Sort"]),
                        Fonds = new Fonds
                        {
                            FondsID = Convert.ToInt32(dr["FondsID"])
                        },
                        ArchiveKind = new ArchiveKind
                        {
                            ArchiveKindID = dr["ArchiveKindID"].ToString()
                        }
                    });
                }
            }
            return archiveTypeList;
        }

        public ArchiveType GetModel(int archiveTypeID)
        {
            ArchiveType archiveType = null;
            using(IDataReader dr = dbProvider.ExecuteDataReader(@"
SELECT 
    ArchiveTypeID,
    ParentID,
    TypeName,
    Sort,
    FondsID,
    ArchiveKindID 
FROM archive_type
WHERE ArchiveTypeID=?ArchiveTypeID
",
                dbProvider.CreateParameter("?ArchiveTypeID", DbType.Int32, archiveTypeID)))
            {
                if (dr.Read())
                {
                    archiveType = new ArchiveType
                    {
                        ArchiveTypeID = Convert.ToInt32(dr["ArchiveTypeID"]),
                        ParentID = Convert.ToInt32(dr["ParentID"]),
                        TypeName = dr["TypeName"].ToString(),
                        Sort = Convert.ToInt32(dr["Sort"]),
                        Fonds = new Fonds
                        {
                            FondsID = Convert.ToInt32(dr["FondsID"])
                        },
                        ArchiveKind = new ArchiveKind
                        {
                            ArchiveKindID = dr["ArchiveKindID"].ToString()
                        }
                    };
                }
            }

            return archiveType;
        }

        public void Add(ArchiveType archiveType)
        {
            dbProvider.ExecuteNonQuery(@"
INSERT INTO archive_type 
(ParentID,TypeName,Sort,FondsID,ArchiveKindID) 
VALUES 
(?ParentID,?TypeName,?Sort,?FondsID,?ArchiveKindID)",
                dbProvider.CreateParameter("?ParentID", DbType.Int32, archiveType.ParentID),
                dbProvider.CreateParameter("?TypeName", DbType.AnsiString, archiveType.TypeName),
                dbProvider.CreateParameter("?Sort", DbType.Int32, archiveType.Sort),
                dbProvider.CreateParameter("?FondsID", DbType.Int32, archiveType.Fonds.FondsID),
                dbProvider.CreateParameter("?ArchiveKindID", DbType.AnsiString, archiveType.ArchiveKind.ArchiveKindID));
        }

        public void Update(ArchiveType archiveType)
        {
            dbProvider.ExecuteNonQuery(@"
UPDATE archive_type
SET TypeName=?TypeName
WHERE ArchiveTypeID=?ArchiveTypeID
",
                dbProvider.CreateParameter("?TypeName", DbType.AnsiString, archiveType.TypeName),
                dbProvider.CreateParameter("?ArchiveTypeID", DbType.Int32, archiveType.ArchiveTypeID));
        }

        public int countByArchiveTypeID(int archiveTypeID)
        {
            return Convert.ToInt32(dbProvider.ExecuteScalar(@"
SELECT 
    (SELECT COUNT(*) FROM archive_v WHERE ArchiveTypeID=?ArchiveTypeID) + 
    (SELECT COUNT(*) FROM archive_f WHERE ArchiveTypeID=?ArchiveTypeID)",
                dbProvider.CreateParameter("?ArchiveTypeID", DbType.Int32, archiveTypeID)));
        }

        public void Delete(int archiveTypeID)
        {
            dbProvider.ExecuteNonQuery("DELETE FRORM archive_type WHERE ArchiveTypeID=?ArchiveTypeID",
                dbProvider.CreateParameter("?ArchiveTypeID", DbType.Int32, archiveTypeID));
        }
        
    }
}

